﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// DBOperation 主要包含跟数据库操作相关的方法
/// </summary>
public class DBOperation
{
    public  SqlTransaction trans;
	public DBOperation()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    /// <summary>
    /// 该方法主要用于小数据量快速查询，返回一个SqldataReader,使用该方法后注意关闭连接
    /// </summary>
    /// <params name="sqlString">数据库查询字符串</params>
    /// <returns>返回一个SqlDataReader对象</returns>
    public  SqlDataReader  GetDataReader(string sqlString)
    {      
        
        try
        {
            SqlConnection conn=DBConnection.CreateConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            SqlDataReader sdr = cmd.ExecuteReader();  
            return sdr;          
        }
        catch (Exception ex)
        {
            throw (ex);
        }     
       
    }
    
    /// <summary>
    /// 根据sql查询语句获取DataSet数据集
    /// </summary>
    /// <param name="sqlString">sql查询语句</param>
    /// <param name="tableName">存储数据的数据表名</param>
    /// <returns>返回一个dataset</returns>
    public DataSet GetDataSet(string sqlString,string tableName)
    {   
        DataSet tempDataset = new DataSet();
        try
        {           
            SqlConnection conn = DBConnection.CreateConnection();
            conn.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlString, conn);
            adapter.Fill(tempDataset, tableName);
            conn.Close();
        }
        catch (Exception ex)
        {
            throw (ex);
        }        
        return tempDataset;

    }
    /// <summary>
    /// 根据sql语句查询单条数据
    /// </summary>
    /// <param name="sqlString">sql查询语句</param>
    /// <returns>返回DataRow对象</returns>
    public DataRow GetDateRow(string sqlString)
    {
        DataSet tempDS = GetDataSet(sqlString,"temp");
        tempDS.CaseSensitive = false;
        DataRow tempDataRow;
        if (tempDS.Tables[0].Rows.Count > 0)
        {
            tempDataRow = tempDS.Tables[0].Rows[0];
            return tempDataRow;
        }
        else
        {
            return null;
        }

    }
    /// <summary>
    /// 该方法用于执行一组sql语句，可用于数据库的事务性操作,sql语句组成一个ArrayList对象作为方法参数
    /// </summary>
    /// <param name="sqlStrings">ArrayList类型的一组sql语句</param>
    /// <returns>执行成功返回真，否则假</returns>
    public Boolean ExcuteSqls(ArrayList sqlStrings)
    {
        bool success;
        SqlConnection conn=DBConnection.CreateConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        trans = conn.BeginTransaction();
        cmd.Transaction = trans;
        try 
        { 
            foreach(string Str in sqlStrings)
            {
                cmd.CommandText = Str;
                cmd.ExecuteNonQuery();
            }
            trans.Commit();
           success=true;
        }
        catch(Exception ex)
        {
            
            success = false;
            trans.Rollback();
            throw (ex);
        }
        finally
        {
            conn.Close();
        }
        return success;
 
    }
    /// <summary>
    /// 该方法主要用于执行非事务性的、非查询的sql语句
    /// </summary>
    /// <param name="sqlstring">sql插入、更新、删除语句</param>
    /// <returns>成功返回TRUE，否则返回FALSE</returns>
    public Boolean ExcuteSql(string sqlString)
    {
        bool status;
        SqlConnection conn=DBConnection.CreateConnection();
        conn.Open();
        try
        {
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            cmd.ExecuteNonQuery();            
            status=true;
           
        }
        catch 
        {
            status =false;
        }
        finally
        {
            conn.Close();
        }
        return status;
    }
    /// <summary>
    /// 单个值查询，返回首行首列
    /// </summary>
    /// <param name="strSql">查询字符串</param>
    /// <returns>返回object类的查询结果</returns>
    public object ExcuteScalar(String strSql)
    {
        SqlConnection conn = DBConnection.CreateConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand(strSql,conn);
        object temp = null;
        try
        {
            temp = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            string exMsg = ex.Message.ToString();
        }
        finally
        {
            conn.Close();
            
        }
        return temp;
        
    }
    /// <summary>
    /// 触发创建新表
    /// </summary>
    public static void CreateTable()
    {
        SqlConnection conn = DBConnection.CreateConnection();
        conn.Open();
        string year =Convert.ToString( DateTime.Now.Year);
        string strSql = "select DATAYEAR from DATAYEAR where dataYear=@year";
      
            
        SqlCommand cmd = new SqlCommand(strSql, conn);

        
        cmd.Parameters.Add("@year", SqlDbType.VarChar, 4);
        cmd.Parameters["@year"].Value = year;
        SqlTransaction trans = conn.BeginTransaction();
        cmd.Transaction = trans;
        try
        {
            object result = cmd.ExecuteScalar();
            if (result == null)
            {

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "createTable";
                cmd.ExecuteNonQuery();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert DATAYEAR(DATAYEAR)values('" + year + "')";
                cmd.ExecuteNonQuery();
                trans.Commit();
                
            }
        }
        catch
        {
            trans.Rollback();
            HttpContext.Current.Response.Redirect("/initialError.htm");
        }
        finally
        {
            conn.Close();
        }
    }

}

   

